import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.junit.Test;

public class JDBCTest {
	public static void main(String[] args) {
		// Shift+Alt+A
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			// 1、加载驱动Class.forName("");
			Class.forName("com.mysql.jdbc.Driver");
			// 2、获得连接对象Connection
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=UTF-8", "root", "1234");
			// 3、写sql语句
			String sql = "SELECT id,name,age,gender FROM user";
			// 4、创建Statement
			statement = connection.prepareStatement(sql);
			// 5、执行sql语句
			// (1) 更新类（更改了表里面数据）：delete/update/insert executeUpdate()
			// 返回值：int，表示你影响的行数
			// (2)查询（没有改变表里面数据）: select executeQuery()
			// 返回值：结果集ResultSet
			resultSet = statement.executeQuery();
			ArrayList<User> list = new ArrayList<User>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				User user = new User(id, name, age, gender);
				list.add(user);
			}

			for (User user : list) {
				System.out.println(user);
			}
			// 6、关闭连接
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	@Test
	public void testSelect() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,name,age,gender FROM user";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			ArrayList<User> list = new ArrayList<User>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				User user = new User(id, name, age, gender);
				list.add(user);
			}

			for (User user : list) {
				System.out.println(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
	}
	
	@Test
	public void testDelete() {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM user WHERE id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, 1);
			int count = statement.executeUpdate();
			// 打印执行的sql语句
			System.out.println(statement);
			if (count == 1) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
	}
	
	@Test
	public void testInsert() {
		String name = "王五";
		int age = 23;
		String gender = "男";
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT user(name,age,gender) VALUES(?,?,?)";
			statement = connection.prepareStatement(sql);
			statement.setString(1, name);
			statement.setInt(2, age);
			statement.setString(3, gender);
			int count = statement.executeUpdate();
			// 打印执行的sql语句
			System.out.println(statement);
			if (count == 1) {
				System.out.println("添加成功");
			} else {
				System.out.println("添加失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
	}
}
